﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.IO;
using System.IO.Compression;
using System.Xml;
using System.Xml.Serialization;
using Pub.Tools;
using WCFContracts;
using System.ServiceModel;
using System.Runtime.Remoting;

namespace Pub.Lib
{
    public class DBAccess : MarshalByRefObject
    {
        private SqlConnection con;
        private SqlCommand comm;
        private SqlDataAdapter da;

        private OleDbConnection OleDbCon;
        private OleDbCommand OleDbComm;
        private OleDbDataAdapter OleDbDA;

        private DataSet ds, Oleds;
        public DBAccess()
        {
            //   Console.Write("构造方法 ");
        }

        /// <summary>
        /// 检查是否是系统用户SYSTEM
        /// </summary>
        /// <param name="ArrAuth"></param>
        /// <param name="strMess"></param>
        /// <returns></returns>
        private Boolean CheckSysUser(string ArrAuth, ref string strMess)
        {
            Boolean bl = false; //默认校验不通过
            string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);
            string strUserPass = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 1);
            string strUserGuid = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 2);


            string ymd = string.Format("{0:yyyyMMdd}", System.DateTime.Now);
            if (strUserCode.ToLower() == "SYSTEM".ToLower() && (strUserPass == ymd)) //系统预置用户,同时避免死循环
            {
                return true;
            }

            if ((strUserPass == string.Empty) && (strUserGuid == string.Empty))
            {
                strMess = string.Format("用户{0}身份校验失败,请检查用户名和密码.", strUserCode);
            }
            return bl;

        }

        /// <summary>
        /// 校验用户名和口令
        /// </summary>
        /// <param name="strUser">用户名</param>
        /// <param name="strPass">口令</param>
        /// <returns></returns>
        public Boolean CheckUser(string ArrAuth, ref string strMess)
        {
            string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);
            string strUserPass = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 1);
            string strUserGuid = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 2);

            if (CheckSysUser(ArrAuth, ref strMess)) //系统预置用户,同时避免死循环
            {
                return true;
            }


            Boolean bl = false;//默认校验不通过

            string dictkey = strUserCode + "_" + strUserGuid;

            if (PubVar.DicLoginUser.ContainsKey(dictkey))
            {
                //存在用户,检查是否超时
                DateTime histime = PubVar.DicLoginUser[dictkey];
                TimeSpan ts = System.DateTime.Now - histime;

                //大于10分,算超时,删除历史
                if (ts.TotalMinutes > 10)
                {
                    PubVar.DicLoginUser.Remove(dictkey);
                }
                else
                {
                    PubVar.DicLoginUser[dictkey] = System.DateTime.Now;
                    return true;
                }
            }

            try
            {
                if (PubVar.Domain == string.Empty)
                {
                    //数据库验证
                    string strsql = string.Format("select * from sysUser Where cUserCode = '{0}'", strUserCode);
                    string[] strArray = { "SYSTEM", string.Format("{0:yyyyMMdd}", System.DateTime.Now) };
                    string strAuth = string.Join(PubVar.SysSplit, strArray, 0, strArray.Length);

                    DataTable dt = GetDataTable(strAuth, strsql, PubVar.ConnMain, ref strMess);
                    if ((dt != null) && (dt.Rows.Count > 0))
                    {
                        string strPWD = DESEncrypt.Encrypt(strUserPass);
                        string dtpwd = dt.Rows[0]["cUserPWD"].ToString();
                        if (dtpwd == strPWD)
                        {
                            bl = true;
                        }
                    }
                }
                else
                {
                    //ad验证
                    bl = ADTools.ADAuthUser(PubVar.Domain, strUserCode, strUserPass, ref  strMess);
                }
                if (!bl)
                {
                    strMess = string.Format("用户{0}身份校验失败,请检查用户名和密码.\r\n{1}", strUserCode, strMess);
                    return false;
                }
                else
                {
                    //保存到历史数据
                    PubVar.DicLoginUser.Add(dictkey, System.DateTime.Now);
                }
            }
            catch (Exception ex)
            {
                strMess = ex.Message.ToString();
                strMess = string.Format("用户{0}身份校验失败,{1}", strUserCode, strMess);
                //throw ex;
            }
            return bl;
        }


        /// <summary>
        ///  返回(SQL语句、连接名称、Table名称)生成的DataSet
        /// </summary>
        /// <param name="ArrAuth">认证字符串</param>
        /// <param name="strSql">SQL语句</param>
        /// <param name="strConnName">连接名称</param>
        /// <param name="tabName">Table名称</param>
        /// <param name="strMess">返回错误提示</param>
        /// <returns>返回DataSet</returns>
        private DataSet GetDateSet(string ArrAuth, string strSql, string strConnName, string tabName, ref string strMess) //主要使用源
        {
            //记时
            DateTime startdt = System.DateTime.Now;
            string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);


            //检查用户是否合法
            if (!CheckUser(ArrAuth, ref  strMess)) { return null; }


            try
            {
                string ConnectionProviderName = string.Empty;
                string ConnectionString = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

                switch (ConnectionProviderName)
                {
                    case "System.Data.SqlClient":
                        {
                            con = new SqlConnection(ConnectionString);
                            da = new SqlDataAdapter(strSql, con);
                            ds = new DataSet();
                            if (tabName == string.Empty)
                            {
                                da.FillSchema(ds, SchemaType.Source);
                                da.Fill(ds);
                            }
                            else
                            {
                                da.FillSchema(ds, SchemaType.Source, tabName);
                                da.Fill(ds, tabName);
                            }
                            da.Dispose();
                            con.Dispose();
                            break;
                        }
                    default:
                        {
                            OleDbCon = new OleDbConnection(ConnectionString);
                            OleDbDA = new OleDbDataAdapter(strSql, OleDbCon);
                            ds = new DataSet();
                            if (tabName == string.Empty)
                            {
                                OleDbDA.FillSchema(ds, SchemaType.Source);
                                OleDbDA.Fill(ds);
                            }
                            else
                            {
                                OleDbDA.FillSchema(ds, SchemaType.Source, tabName);
                                OleDbDA.Fill(ds, tabName);
                            }
                            OleDbDA.Dispose();
                            OleDbCon.Dispose();
                            break;
                        }
                } //switch
                if (PubVar.SysUpdateSQL)
                {
                    TimeSpan ts = System.DateTime.Now - startdt;
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【{0}】【Server 读 SQL】耗时{1:0.000}秒.连接名称:{2},SQL语句:\r\n{3}\r\n", strUserCode, ts.TotalSeconds, strConnName, strSql));
                }

            }
            catch (Exception ex)
            {
                SysTools.WriteTxt(PubVar.LogFile, string.Format("【{0}】【Server 读 SQL错误】连接名称:{1},SQL语句:\r\n{2}\r\n【错误提示】:\r\n{3}\r\n", strUserCode, strConnName, strSql, ex.Message.ToString()));
                throw ex;
            }
            return ds;
        }


        public Boolean ExecSQL(string strSql, string strConnName, ref string strMess) //主要执行SQL源
        {
            Boolean bl = true;

            //记时
            DateTime startdt = System.DateTime.Now;
            try
            {
                string ConnectionProviderName = string.Empty;
                string ConnectionString = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

                switch (ConnectionProviderName)
                {
                    case "System.Data.SqlClient":
                        {
                            con = new SqlConnection(ConnectionString);
                            comm = new SqlCommand(strSql, con);
                            con.Open();
                            comm.ExecuteNonQuery();
                            con.Close();
                            comm.Dispose();
                            con.Dispose();
                            break;
                        }
                    default:
                        {
                            //OleDb
                            OleDbCon = new OleDbConnection(ConnectionString);
                            OleDbComm = new OleDbCommand(strSql, OleDbCon);
                            OleDbCon.Open();
                            OleDbComm.ExecuteNonQuery();
                            OleDbCon.Close();
                            OleDbComm.Dispose();
                            OleDbCon.Dispose();
                            break;
                        }
                } //switch
                if (PubVar.SysUpdateSQL)
                {
                    TimeSpan ts = System.DateTime.Now - startdt;
                    SysTools.WriteTxt(PubVar.LogFile, string.Format("【Server 执行 SQL】耗时{0:0.000}秒.连接名称:{1},SQL语句:\r\n{2}\r\n", ts.TotalSeconds, strConnName, strSql));
                }

            }
            catch (Exception ex)
            {
                bl = false;
                strMess = ex.Message;
                SysTools.WriteTxt(PubVar.LogFile, string.Format("【Server 执行 SQL错误】连接名称:{0},SQL语句:\r\n{1}\r\n【错误提示】:\r\n{2}\r\n", strConnName, strSql, ex.Message.ToString()));
                //                throw ex;
            }

            return bl;
        }

        /// <summary>
        /// 返回(SQL语句、连接名称)生成的DateSet
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回(SQL语句、连接名称)生成的DateSet</returns>
        private DataSet GetDateSet(string ArrAuth, string strSql, string strConnName, ref string strMess)  //主要使用源
        {
            DataSet ds = GetDateSet(ArrAuth, strSql, strConnName, string.Empty, ref strMess);
            return ds;
        }



        /// <summary>
        /// 返回(SQL语句、连接名称)生成的DateSet
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回(SQL语句、连接名称)生成的DateSet</returns>
        private DataSet GetDateSet(string strSql, SqlConnection SqlConn)
        {
            try
            {
                da = new SqlDataAdapter(strSql, SqlConn);
                ds = new DataSet();
                da.FillSchema(ds, SchemaType.Source);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
            }
            return ds;
        }

        private DataSet GetDateSetOleDb(string strSql, OleDbConnection OleDbConn)
        {
            DataSet _Oleds = new DataSet();
            try
            {
                OleDbDataAdapter OleDbda = new OleDbDataAdapter(strSql, OleDbConn);
                OleDbda.FillSchema(_Oleds, SchemaType.Source);
                OleDbda.Fill(_Oleds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return _Oleds;
        }

        /// <summary>
        /// 返回(SQL语句、连接名称、Table名称)生成的DataTable
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <param name="tabName">Table名称</param>
        /// <returns>返回(SQL语句、连接名称、Table名称)生成的DataTable</returns>
        public DataTable GetDataTable(string ArrAuth, string strSql, string strConnName, string tabName, ref string strMess)
        {
            if (tabName == string.Empty)
            {
                return GetDataTable(ArrAuth, strSql, strConnName, ref strMess);
            }
            else
            {
                DataSet dsSet = GetDateSet(ArrAuth, strSql, strConnName, tabName, ref strMess);
                return dsSet.Tables[tabName];
            }
        }


        /// <summary>
        /// 返回(SQL语句、连接名称)生成的DataTable
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回(SQL语句、连接名称)生成的DataTable</returns>
        public DataTable GetDataTable(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            DataSet dsSet = GetDateSet(ArrAuth, strSql, strConnName, ref strMess);
            return dsSet.Tables[0];
        }


        /// <summary>
        /// 返回(SQL语句、连接名称)生成的DataTable
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回(SQL语句、连接名称)生成的DataTable</returns>
        public DataTable GetDataTable(string strSql, SqlConnection SqlConn)
        {
            DataSet dsSet = GetDateSet(strSql, SqlConn);
            return dsSet.Tables[0];
        }

        public DataTable GetDataTableOleDb(string strSql, OleDbConnection OleConn)
        {
            DataSet dsSet = GetDateSetOleDb(strSql, OleConn);
            return dsSet.Tables[0];
        }

        /// <summary>
        /// 更新系列化db
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="strxml">压缩的XML</param>
        /// <param name="strConnName">连接名称</param>
        /// <param name="dbname">更新的Table名称</param>
        /// <param name="dbfields">更新的字段</param>
        /// <param name="strMess">错误提示</param>
        /// <returns>返回是否成功更新</returns>
        public Boolean SaveDT(string ArrAuth, string strSQL, string strxml, string strConnName, string dbname, string dbfields, ref string strMess)
        {
            //1.解压字符串
            Boolean bl = false;
            try
            {
                string strdt = ZipString.DecompressString(strxml);

                //2.转换为dt
                DataTable dt = XmlDB.SerializeXmlToDataTable(strdt);

                bl = UpdateDb(ArrAuth, strSQL, dt, strConnName, dbname, dbfields);
            }
            catch (Exception ex)
            {
                strMess = ex.Message.ToString();
                string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);
                SysTools.WriteTxt(PubVar.LogFile, string.Format("【{0}】【Server 提交 SQL错误】提示:{1}.\r\n连接名称:{2},SQL语句:\r\n{3}\r\n", strUserCode, ex.Message.ToString(), strConnName, strSQL));
                //throw ex;
            }
            return bl;
        }



        public Boolean UpdateDbSerializeOneTable(string strSQL, string strxml, string strConnName)
        {
            //1.解压字符串
            string strdt = ZipString.DecompressString(strxml);

            //2.转换为dt
            //   DataTable dt = DeserializeDataTable(strdt);


            return UpdateDbOneTable(strSQL, XmlDB.SerializeXmlToDataTable(strdt), strConnName);
        }


        private SqlDbType GetDBType(System.Type theType)
        {
            SqlParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new SqlParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {        //Try brute force    
                try
                {
                    p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                }
                catch
                // catch (Exception ex)
                {                //Do Nothing    
                }
            }
            return p1.SqlDbType;
        }

        private OleDbType GetOleDBType(System.Type theType)
        {
            OleDbParameter p1;
            System.ComponentModel.TypeConverter tc;
            p1 = new OleDbParameter();
            tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType);
            if (tc.CanConvertFrom(theType))
            {
                p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
            }
            else
            {        //Try brute force    
                try
                {
                    p1.DbType = (DbType)tc.ConvertFrom(theType.Name);
                }
                catch
                // catch (Exception ex)
                {                //Do Nothing    
                }
            }
            return p1.OleDbType;
        }

        private int GetColumnWidth(DataColumn col)
        {
            int intWidth = col.MaxLength;
            string strType = col.DataType.Name;
            if (strType.ToLower().IndexOf("int") > -1)
            {
                intWidth = 4;
            }
            return intWidth;
        }


        /// <summary>
        /// 自动生成DataSet新增、删除、更新命令
        /// </summary>
        /// <param name="scConnection">连接</param>
        /// <param name="dbname">Table名称</param>
        /// <param name="dbfields">字段</param>
        /// <param name="updateAdapter">更新数据集</param>
        private void SetCommand(SqlConnection scConnection, string dbname, string dbfields, ref SqlDataAdapter updateAdapter)
        {
            string strsql = "Select top 0 " + dbfields + " from " + dbname;
            string strPK = string.Empty;
            string strDel = string.Empty;
            string strUpdate = string.Empty;
            string strInsert = string.Empty;
            string strSelect = string.Empty;
            DataTable dt = GetDataTable(strsql, scConnection);
            DataColumn[] cols = dt.PrimaryKey;
            if (cols.Length == 0) { return; }
            for (int i = 0; i < cols.Length; i++)
            {
                string strtmp = cols[i].ColumnName + " = @old" + cols[i].ColumnName;
                strPK = strPK == string.Empty ? " Where " + strtmp : strPK + " And " + strtmp;
            }
            strDel = "DELETE FROM " + dbname + strPK;

            SqlCommand Del_command = new SqlCommand(strDel, scConnection);
            Del_command.UpdatedRowSource = UpdateRowSource.None;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!dt.Columns[i].AutoIncrement)
                {
                    string strtmp = dt.Columns[i].ColumnName + " = @" + dt.Columns[i].ColumnName;
                    strUpdate = strUpdate == string.Empty ? strtmp : strUpdate + " , " + strtmp;

                    strtmp = dt.Columns[i].ColumnName;

                    strSelect = strSelect == string.Empty ? strtmp : strSelect + "," + strtmp;
                    strInsert = strInsert == string.Empty ? "@" + strtmp : strInsert + ",@" + strtmp;
                }
            }

            strUpdate = "UPDATE " + dbname + " Set " + strUpdate + strPK;
            strInsert = "INSERT INTO " + dbname + " (" + strSelect + ") VALUES (" + strInsert + " )";

            strSelect = "Select " + strSelect + " FROM " + dbname + strPK;

            SqlCommand Insert_command = new SqlCommand(strInsert, scConnection); //插入SqlCommand
            Insert_command.UpdatedRowSource = UpdateRowSource.None;

            SqlCommand Update_command = new SqlCommand(strUpdate, scConnection); //更新SqlCommand
            Update_command.UpdatedRowSource = UpdateRowSource.None;

            SqlCommand Select_command = new SqlCommand(strSelect, scConnection); //选择SqlCommand
            Select_command.UpdatedRowSource = UpdateRowSource.None;


            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!dt.Columns[i].AutoIncrement)
                {
                    int intLength = GetColumnWidth(dt.Columns[i]);
                    SqlDbType retval = GetDBType(dt.Columns[i].GetType());

                    Update_command.Parameters.Add("@" + dt.Columns[i].ColumnName, retval, intLength, dt.Columns[i].ColumnName);
                    Insert_command.Parameters.Add("@" + dt.Columns[i].ColumnName, retval, intLength, dt.Columns[i].ColumnName);
                }
            }

            for (int i = 0; i < cols.Length; i++)
            {

                int intLength = GetColumnWidth(cols[i]);
                SqlDbType retval = GetDBType(cols[i].GetType());


                SqlParameter Del_parameter = Del_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Del_parameter.SourceVersion = DataRowVersion.Original;

                SqlParameter Update_parameter = Update_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Update_parameter.SourceVersion = DataRowVersion.Original;


                SqlParameter Select_parameter = Select_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Select_parameter.SourceVersion = DataRowVersion.Original;
            }
            updateAdapter.SelectCommand = Select_command;
            updateAdapter.DeleteCommand = Del_command;
            updateAdapter.UpdateCommand = Update_command;
            updateAdapter.InsertCommand = Insert_command;
        }


        /// <summary>
        /// 自动生成DataSet新增、删除、更新命令
        /// </summary>
        /// <param name="scConnection">连接</param>
        /// <param name="dbname">Table名称</param>
        /// <param name="dbfields">字段</param>
        /// <param name="updateAdapter">更新数据集</param>
        private void SetOleDBCommand(OleDbConnection scConnection, string dbname, string dbfields, ref OleDbDataAdapter updateAdapter)
        {
            string strsql = "Select top 1 " + dbfields + " from " + dbname;
            string strPK = string.Empty;
            string strDel = string.Empty;
            string strUpdate = string.Empty;
            string strInsert = string.Empty;
            string strSelect = string.Empty;

            DataTable dt = GetDataTableOleDb(strsql, scConnection);
            DataColumn[] cols = dt.PrimaryKey;
            if (cols.Length == 0) { return; }
            for (int i = 0; i < cols.Length; i++)
            {
                string strtmp = cols[i].ColumnName + " = @old" + cols[i].ColumnName;
                strPK = strPK == string.Empty ? " Where " + strtmp : strPK + " And " + strtmp;
            }
            strDel = "DELETE FROM " + dbname + strPK;

            OleDbCommand Del_command = new OleDbCommand(strDel, scConnection);
            Del_command.UpdatedRowSource = UpdateRowSource.None;

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!dt.Columns[i].AutoIncrement)
                {
                    string strtmp = dt.Columns[i].ColumnName + " = @" + dt.Columns[i].ColumnName;
                    strUpdate = strUpdate == string.Empty ? strtmp : strUpdate + " , " + strtmp;

                    strtmp = dt.Columns[i].ColumnName;

                    strSelect = strSelect == string.Empty ? strtmp : strSelect + "," + strtmp;
                    strInsert = strInsert == string.Empty ? "@" + strtmp : strInsert + ",@" + strtmp;
                }
            }

            strUpdate = "UPDATE " + dbname + " Set " + strUpdate + strPK;
            strInsert = "INSERT INTO " + dbname + " (" + strSelect + ") VALUES (" + strInsert + " )";

            strSelect = "Select " + strSelect + " FROM " + dbname + strPK;

            OleDbCommand Insert_command = new OleDbCommand(strInsert, scConnection); //插入SqlCommand
            Insert_command.UpdatedRowSource = UpdateRowSource.None;

            OleDbCommand Update_command = new OleDbCommand(strUpdate, scConnection); //更新SqlCommand
            Update_command.UpdatedRowSource = UpdateRowSource.None;

            OleDbCommand Select_command = new OleDbCommand(strSelect, scConnection); //选择SqlCommand
            Select_command.UpdatedRowSource = UpdateRowSource.None;


            for (int i = 0; i < dt.Columns.Count; i++)
            {
                if (!dt.Columns[i].AutoIncrement)
                {
                    int intLength = GetColumnWidth(dt.Columns[i]);
                    OleDbType retval = GetOleDBType(dt.Columns[i].GetType());

                    Update_command.Parameters.Add("@" + dt.Columns[i].ColumnName, retval, intLength, dt.Columns[i].ColumnName);
                    Insert_command.Parameters.Add("@" + dt.Columns[i].ColumnName, retval, intLength, dt.Columns[i].ColumnName);
                }
            }

            for (int i = 0; i < cols.Length; i++)
            {

                int intLength = GetColumnWidth(cols[i]);
                OleDbType retval = GetOleDBType(cols[i].GetType());

                OleDbParameter Del_parameter = Del_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Del_parameter.SourceVersion = DataRowVersion.Original;

                OleDbParameter Update_parameter = Update_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Update_parameter.SourceVersion = DataRowVersion.Original;


                OleDbParameter Select_parameter = Select_command.Parameters.Add("@old" + cols[i].ColumnName, retval, intLength, cols[i].ColumnName);
                Select_parameter.SourceVersion = DataRowVersion.Original;
            }
            updateAdapter.SelectCommand = Select_command;
            updateAdapter.DeleteCommand = Del_command;
            updateAdapter.UpdateCommand = Update_command;
            updateAdapter.InsertCommand = Insert_command;
        }

        public Boolean UpdateDb(string ArrAuth, string strSQL, DataTable updatedt, string strConnName, string dbname, string dbfields)
        {
            Boolean bl = false;
            //记时
            DateTime startdt = System.DateTime.Now;



            string ConnectionProviderName = string.Empty;
            string ConnectionString = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

            //新增数
            int AddedNum = 0;

            //修改数
            int ModifiedNum = 0;

            //删除数
            int DeletedNum = 0;

            if (PubVar.SysUpdateSQL)
            {
                //新增数
                DataTable dtAdded = updatedt.GetChanges(DataRowState.Added);
                if (dtAdded != null)
                {
                    AddedNum = dtAdded.Rows.Count;
                    dtAdded.Dispose();
                }

                //修改数
                DataTable dtModified = updatedt.GetChanges(DataRowState.Modified);
                if (dtModified != null)
                {
                    ModifiedNum = dtModified.Rows.Count;
                    dtModified.Dispose();
                }

                //删除数
                DataTable dtDeleted = updatedt.GetChanges(DataRowState.Deleted);
                if (dtDeleted != null)
                {
                    DeletedNum = dtDeleted.Rows.Count;
                    dtDeleted.Dispose();
                }
            }

            switch (ConnectionProviderName)
            {
                case "System.Data.SqlClient":
                    {
                        bl = UpdateDbSQL(strSQL, updatedt, strConnName, dbname, dbfields);
                        break;
                    }
                default:
                    {
                        bl = UpdateDbOleDB(strSQL, updatedt, strConnName, dbname, dbfields);
                        break;
                    }
            } //switch
            if (PubVar.SysUpdateSQL)
            {
                TimeSpan ts = System.DateTime.Now - startdt;
                string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);
                SysTools.WriteTxt(PubVar.LogFile, string.Format("【{0}】【Server 提交 SQL】新增:{1},修改:{2},删除:{3}.共耗时{4:0.000}秒.连接名称:{5},SQL语句:\r\n{6}\r\n", strUserCode, AddedNum, ModifiedNum, DeletedNum, ts.TotalSeconds, strConnName, strSQL));
            }

            return bl;
        }

        /// <summary>
        /// 更新SQL SERVER数据库
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="updatedt">需更新的DataTable</param>
        /// <param name="strConnName">连接名称</param> 
        /// <param name="dbname">Table名称</param>
        /// <param name="dbfields">更新字段</param> 
        /// <returns>是否成功更新数据库</returns>
        public Boolean UpdateDbSQL(string strSQL, DataTable updatedt, string strConnName, string dbname, string dbfields)
        {
            if (dbname == string.Empty)
            {
                throw new Exception("提交数据失败,TableName不能为空.");
            }
            Boolean bl = true;
            DataSet ds2 = new DataSet();
            DataSet dsSet = new DataSet();

            SqlDataAdapter sdaAdapter = null;
            //建立Connection


            string ConnectionProviderName = string.Empty;
            string strConn = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

            SqlConnection scConnection = new SqlConnection(strConn);
            //建立Command
            SqlCommand scCommand = scConnection.CreateCommand();
            scCommand.CommandText = strSQL;
            //建立Adapter
            sdaAdapter = new SqlDataAdapter(scCommand);

            //该对象负责生成用于更新数据库的SQL语句，不必自己创建这些语句
            SqlCommandBuilder scbBuilder = new SqlCommandBuilder(sdaAdapter);
            //    sdaAdapter.Fill(ds2, "DW_Subject");


            //旧方法,已经取消
            //    sdaAdapter.Fill(ds2, dbname);
            //   ds2.Tables[0].Merge(updatedt);


            ds2.Tables.Add(updatedt);
            ds2.Tables[0].TableName = dbname;

            sdaAdapter.UpdateBatchSize = 10;


            SetCommand(scConnection, dbname, dbfields, ref  sdaAdapter);


            scConnection.Open();
            SqlTransaction myTrans = scConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            sdaAdapter.SelectCommand.Transaction = myTrans;
            if (sdaAdapter.InsertCommand != null)
            {
                sdaAdapter.InsertCommand.Transaction = myTrans;
            }
            if (sdaAdapter.UpdateCommand != null)
            {
                sdaAdapter.UpdateCommand.Transaction = myTrans;
            }
            if (sdaAdapter.DeleteCommand != null)
            {
                sdaAdapter.DeleteCommand.Transaction = myTrans;
            }
            try
            {
                //sdaAdapter.Update(ds2, dsSet.Tables[0].TableName);
                sdaAdapter.Update(ds2, dbname);
                myTrans.Commit();
            }
            catch (Exception e)
            {
                bl = false;
                try
                {
                    myTrans.Rollback();
                    throw e;
                }
                catch (SqlException ex)
                {
                    if (myTrans.Connection != null)
                    {
                        throw ex;
                    }
                    else
                    {
                        throw e;
                    }
                }

            }
            finally
            {
                myTrans.Dispose();
                scConnection.Close();
            }
            sdaAdapter.Dispose();
            scConnection.Close();
            return bl;
        }

        /// <summary>
        /// 更新OleDB数据库
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="updatedt">需更新的DataTable</param>
        /// <param name="strConnName">连接名称</param> 
        /// <param name="dbname">Table名称</param>
        /// <param name="dbfields">更新字段</param> 
        /// <returns>是否成功更新数据库</returns>
        public Boolean UpdateDbOleDB(string strSQL, DataTable updatedt, string strConnName, string dbname, string dbfields)
        {

            if (dbname == string.Empty)
            {
                throw new Exception("提交数据失败,TableName不能为空.");
            }

            Boolean bl = true;
            DataSet ds2 = new DataSet();
            DataSet dsSet = new DataSet();

            string ConnectionProviderName = string.Empty;
            string strConn = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

            OleDbConnection scConnection = new OleDbConnection(strConn);
            //建立Command
            OleDbCommand scCommand = scConnection.CreateCommand();
            scCommand.CommandText = strSQL;
            //建立Adapter
            OleDbDataAdapter OleAD = new OleDbDataAdapter(scCommand);

            //该对象负责生成用于更新数据库的SQL语句，不必自己创建这些语句
            OleDbCommandBuilder scbBuilder = new OleDbCommandBuilder(OleAD);


            //旧方法,已经取消
            //OleAD.Fill(ds2, dbname);
            //ds2.Tables[0].Merge(updatedt);


            ds2.Tables.Add(updatedt);
            ds2.Tables[0].TableName = dbname;

            //  OleAD.UpdateBatchSize = 10; //OleDB不支持

            SetOleDBCommand(scConnection, dbname, dbfields, ref  OleAD);

            scConnection.Open();
            OleDbTransaction myTrans = scConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            OleAD.SelectCommand.Transaction = myTrans;
            if (OleAD.InsertCommand != null)
            {
                OleAD.InsertCommand.Transaction = myTrans;
            }
            if (OleAD.UpdateCommand != null)
            {
                OleAD.UpdateCommand.Transaction = myTrans;
            }
            if (OleAD.DeleteCommand != null)
            {
                OleAD.DeleteCommand.Transaction = myTrans;
            }
            try
            {
                //sdaAdapter.Update(ds2, dsSet.Tables[0].TableName);
                OleAD.Update(ds2, dbname);
                myTrans.Commit();
            }
            catch (Exception e)
            {
                bl = false;
                try
                {
                    myTrans.Rollback();
                    throw e;
                }
                catch (OleDbException ex)
                {
                    if (myTrans.Connection != null)
                    {
                        throw ex;
                    }
                    else
                    {
                        throw e;
                    }
                }

            }
            finally
            {
                myTrans.Dispose();
                scConnection.Close();
            }
            OleAD.Dispose();
            scConnection.Close();
            return bl;
        }


        /// <summary>
        /// 更新数据库
        /// </summary>
        /// <param name="strSQL">SQL语句</param>
        /// <param name="updatedt">需更新的DataTable</param>
        /// <param name="strConnName">连接名称</param>
        /// <returns></returns>
        private Boolean UpdateDbOneTable(string strSQL, DataTable updatedt, string strConnName)
        {
            Boolean bl = true;
            DataSet ds2 = new DataSet();
            DataSet dsSet = new DataSet();

            SqlDataAdapter sdaAdapter = null;
            //建立Connection

            string ConnectionProviderName = string.Empty;
            string strConn = GetConnectionStringByName(strConnName, ref ConnectionProviderName);

            SqlConnection scConnection = new SqlConnection(strConn);
            //建立Command
            SqlCommand scCommand = scConnection.CreateCommand();
            scCommand.CommandText = strSQL;
            //建立Adapter
            sdaAdapter = new SqlDataAdapter(scCommand);

            //该对象负责生成用于更新数据库的SQL语句，不必自己创建这些语句
            SqlCommandBuilder scbBuilder = new SqlCommandBuilder(sdaAdapter);
            //    sdaAdapter.Fill(ds2, "DW_Subject");
            sdaAdapter.Fill(ds2);

            //  dsSet.Tables[0] = updatedt.Clone();
            //    ds2.Tables[0].Merge(dsSet.Tables[0]);

            ds2.Tables[0].Merge(updatedt);
            sdaAdapter.UpdateBatchSize = 10;

            MsgTools.InfoMsg(sdaAdapter.UpdateCommand.ToString());

            scConnection.Open();
            SqlTransaction myTrans = scConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            sdaAdapter.SelectCommand.Transaction = myTrans;

            try
            {
                //sdaAdapter.Update(ds2, dsSet.Tables[0].TableName);
                sdaAdapter.Update(ds2);
                myTrans.Commit();
            }
            catch (Exception e)
            {
                bl = false;
                try
                {
                    myTrans.Rollback();
                    throw e;
                }
                catch (SqlException ex)
                {
                    if (myTrans.Connection != null)
                    {
                        throw ex;
                    }
                    else
                    {
                        throw e;
                    }
                }

            }
            finally
            {
                myTrans.Dispose();
                scConnection.Close();
            }
            //sdaAdapter.Dispose();
            //scConnection.Close();
            return bl;
        }


        /// <summary>
        /// 获取连接名称的服务器时间
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <returns>返回连接名称的服务器时间</returns>
        public DateTime GetServerTime(string strConnName, ref string strMess)
        {
            string strAuth = "SYSTEM" + PubVar.SysSplit + string.Format("{0:yyyyMMdd}", System.DateTime.Now);
            DataTable dt = GetDataTable(strAuth, "select getdate() as dservertime", strConnName, ref  strMess);
            if (dt != null)
            {
                return SysTools.ToDateTime(dt.Rows[0][0].ToString(), System.DateTime.Now);
            }
            else
            {
                return System.DateTime.Now;
            }
        }


        /// <summary>
        /// 获取(连接名称)的字符串
        /// </summary>
        /// <param name="ConnectionName">连接名称</param>
        /// <param name="returnProviderName">返回连接类型</param>
        /// <returns>返回名称的连接字符串(连接类型)</returns>
        public string GetConnectionStringByName(string ConnectionName, ref string returnProviderName)
        {
            // Assume failure.
            string returnValue = null;
            //     string appconfig = System.IO.Path.Combine(AppDomain.CurrentDomain.SetupInformation.ApplicationBase, "App.config");
            //    System.AppDomain.CurrentDomain.SetData("APP_CONFIG_FILE", appconfig);

            if (!PubVar.DicConn.ContainsKey(ConnectionName))
            {
                // Look for the name in the connectionStrings section.
                ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[ConnectionName];

                // If found, return the connection string and ProviderName String.
                if (settings != null)
                {
                    returnValue = settings.ConnectionString;
                    //替换%APP%\为程序当前目录
                    returnValue = returnValue.Replace(@"%APP%\", AppDomain.CurrentDomain.SetupInformation.ApplicationBase);
                    returnProviderName = settings.ProviderName;
                }
                else
                {
                    if (System.Diagnostics.Process.GetCurrentProcess().ProcessName == "devenv")
                    {
                        object tmpobj = new string[] { string.Empty, string.Empty };
                        PubVar.DicConn.Add(ConnectionName, tmpobj);
                    }
                    throw new Exception(string.Format("connectionStrings name= \"{0}\" 连接无定义,请检查文件:\r\n{1}", ConnectionName, System.AppDomain.CurrentDomain.GetData("APP_CONFIG_FILE").ToString()));
                }
                object obj = new string[] { returnValue, returnProviderName };
                PubVar.DicConn.Add(ConnectionName, obj);
            }
            else
            {
                object obj = PubVar.DicConn[ConnectionName];
                string[] xx = obj as string[];
                returnValue = xx[0];
                returnProviderName = xx[1];

            }
            return returnValue;
        }



        /// <summary>
        /// 获取(连接名称、SQL语句)生成的DateSet生成的字符串
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>返回(连接名称、SQL语句)生成的DateSet生成的字符串</returns>
        private string GetDateSetXml(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            DataSet dsSet = GetDateSet(ArrAuth, strSql, strConnName, ref  strMess);
            return XmlDB.DataToXml(dsSet);
        }

        /// <summary>
        /// 获取(连接名称、SQL语句)生成的DateSet生成的字符串
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <param name="dbname">Table表名</param>
        /// <returns>返回(连接名称、SQL语句)生成的DateSet生成的字符串</returns>
        private string GetDataTableXml(string ArrAuth, string strSql, string strConnName, string dbname, ref string strMess)
        {
            DataTable dt = GetDataTable(ArrAuth, strSql, strConnName, dbname, ref  strMess);
            return XmlDB.DataToXml(dt);
        }

        /// <summary>
        /// 返回(连接名称、SQL语句)生成的DataTable生成的字符串
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        public string GetDataTableXml(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            DataTable dt = GetDataTable(ArrAuth, strSql, strConnName, ref strMess);
            return XmlDB.DataToXml(dt);
        }



        /// <summary>
        /// 序列化的DataTable
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <param name="dbname">Table名称</param>/// 
        /// <returns>经序列化的DataTable的XML</returns>
        private string GetDataTableXmlSerialize(string ArrAuth, string strSql, string strConnName, string dbname, ref string strMess)
        {
            // 序列化DataTable
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);
            XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
            DataTable pDt = GetDataTable(ArrAuth, strSql, strConnName, dbname, ref strMess);
            serializer.Serialize(writer, pDt);
            writer.Close();
            pDt.Dispose();
            return sb.ToString();
        }

        /// <summary>
        /// 序列化的DataTable并压缩
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <param name="dbname">Table名称</param>/// 
        /// <returns>经序列化的DataTable并压缩的XML</returns>
        private string GetDataTableXmlSerializeZip(string ArrAuth, string strSql, string strConnName, string dbname, ref string strMess)
        {
            return ZipString.CompressString(GetDataTableXmlSerialize(ArrAuth, strSql, strConnName, dbname, ref  strMess));
        }



        /// <summary>
        /// 序列化的DataTable
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>经序列化的DataTable的XML</returns>
        public string GetDataTableXmlSerialize(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            // 序列化DataTable
            StringBuilder sb = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(sb);
            XmlSerializer serializer = new XmlSerializer(typeof(DataTable));
            DataTable pDt = GetDataTable(ArrAuth, strSql, strConnName, ref strMess);
            serializer.Serialize(writer, pDt);
            writer.Close();
            pDt.Dispose();
            return sb.ToString();
        }



        /// <summary>
        /// 序列化的DataTable并压缩
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns>经序列化的DataTable并压缩的XML</returns>
        public string GetDataTableXmlSerializeZip(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            return ZipString.CompressString(GetDataTableXmlSerialize(ArrAuth, strSql, strConnName, ref strMess));
        }



        /// <summary>
        /// 返回压缩的由(连接名称、SQL语句)生成的DateSet生成的字符串
        /// </summary>
        /// <param name="strConnName">连接名称</param>
        /// <param name="strSql">SQL语句</param>
        /// <returns></returns>
        private string GetDateSetToXmlZip(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            DataSet dsSet = GetDateSet(ArrAuth, strSql, strConnName, ref  strMess);
            string strdsSet = XmlDB.DataToXml(dsSet);
            return ZipString.CompressString(strdsSet);
        }


        /// <summary>
        /// 压缩的由(SQL语句、连接名称、表格)生成的DataTable生成的字符串
        /// </summary> 
        /// <param name="strSql">SQL语句</param>
        /// <param name="strConnName">连接名称</param>
        /// <param name="tabName">表格</param>
        /// <returns>返回压缩的由(SQL语句、连接名称)生成的DataTable生成的字符串</returns>
        private string GetDataTableXmlZip(string ArrAuth, string strSql, string strConnName, string tabName, ref string strMess)
        {
            return ZipString.CompressString(GetDataTableXml(ArrAuth, strSql, strConnName, tabName, ref  strMess));
        }

        /// <summary>
        /// 压缩的由(SQL语句、连接名称)生成的DataTable生成的字符串
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <param name="strConnName">连接名称</param>
        /// <returns>返回压缩的由(SQL语句、连接名称)生成的DataTable生成的字符串</returns>
        public string GetDataTableXmlZip(string ArrAuth, string strSql, string strConnName, ref string strMess)
        {
            string strResult = string.Empty;
            try
            {
                strResult = ZipString.CompressString(GetDataTableXml(ArrAuth, strSql, strConnName, ref  strMess));
            }
            catch (Exception ex)
            {
                strMess = ex.Message.ToString();
            }
            return strResult;
        }


        public Boolean ChangeUserPasswd(string domain, string userName, string oldPwd, string newPwd, ref string strMess)
        {
            return ADTools.ADChangeUserPasswd(domain, userName, oldPwd, newPwd, ref  strMess);
        }

        public Boolean IChangeUserPasswd(string ArrAuth, ref string strMess)
        {

            //string strAuth = DESEncrypt.Decrypt(strEnAuth);

            ////分解字符串出:0用户名,1密码,2GUID,3新密码
            string strUserCode = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 0);
            string strUserPass = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 1);
            string strUserGuid = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 2);
            string strNewPWD = SysTools.SpiltArr(ArrAuth, PubVar.SysSplit, 3);
            if (PubVar.Domain != string.Empty)
            {
                return ADTools.ADChangeUserPasswd(PubVar.Domain, strUserCode, strUserPass, strNewPWD, ref  strMess);
            }
            else
            {
                //数据库验证
                Boolean bl = false;
                string strsql = string.Format("select * from sysUser Where cUserCode = '{0}'", strUserCode);

                string[] strArray = { "SYSTEM", string.Format("{0:yyyyMMdd}", System.DateTime.Now) };
                string strAuth = string.Join(PubVar.SysSplit, strArray, 0, strArray.Length);

                DataTable dt = GetDataTable(strAuth, strsql, PubVar.ConnMain, ref strMess);
                if ((dt != null) && (dt.Rows.Count > 0))
                {
                    string strPWD = DESEncrypt.Encrypt(strUserPass);
                    string dtpwd = dt.Rows[0]["cUserPWD"].ToString();
                    if (dtpwd == strPWD)
                    {
                        string strENNewPWD = DESEncrypt.Encrypt(strNewPWD);
                        dt.Rows[0]["cUserPWD"] = strENNewPWD;
                        bl = Helper.DBHelper.SaveDataTable(strsql, dt, PubVar.ConnMain, "sysUser", DataAccessType.CS);
                        if (!bl)
                        {
                            strMess = string.Format("用户{0}更改密码失败.", strUserCode);
                        }
                    }
                    else
                    {
                        strMess = string.Format("用户{0}身份校验失败,请检查用户名和密码.", strUserCode);
                    }
                }
                return bl;
            }

        }


        public Boolean IAuthUser(string strEnAuth, ref string strMess)
        {
            return CheckUser(strEnAuth, ref strMess);

            //旧方式,已更改
            // return ADTools.AuthUser(domain, userName, password, ref  strMess);

            //  string strAuth = DESEncrypt.Decrypt(strEnAuth);
            //分解字符串出:域名,用户名,密码
            //string[] strArray = System.Text.RegularExpressions.Regex.Split(strAuth, @"[" + PubVar.SysSplit + "]+");
            //if (strArray.Length < 3)
            //{
            //    return false;
            //}
            //string domain = strArray[0];
            //string userName = strArray[1];
            //string password = strArray[2];

            //return Pub.Tools.ADTools.AuthUser(domain, userName, password, ref  strMess);
        }


        public override object InitializeLifetimeService()
        {
            return null;
        }
    }




}
